using System;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.Globalization;
using System.Net;
using System.Net.Mail;
using System.Net.Mime;
using System.IO;
using System.IO.Compression;
using System.Runtime.InteropServices;
using System.Security;
using System.Security.Cryptography;
using System.Security.Principal;
using System.Text;
using System.Text.RegularExpressions;
using System.Timers;
using System.Web;
using System.Web.Mail;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;

namespace Halide
{
	/// <summary>
	/// The Halide DataRow class is used to load and manipulate an entire row from a database table/view/SP,
	/// and uses Halide.DatabaseRow and Halide.DatabaseItem classes as children.
	/// <para>Note that DataRow is case-sensitive! So when you use methods like "Exec" which send DataRow column values
	/// back to a stored procedure, the column names must match the stored procedure names, including case, which also
	/// must match the destination table names, inclduing case.</para>
	/// <para>In addition, the "Exec" method will not work with stored procedures that use OUTPUT parameters.</para>
	/// </summary>
    /// <example>
    /// Before using the Halide.DataRow class, you must add a connection string to your web.config
    /// file as illustrated below. For more information, see the Halide documentation.
    /// <code lang="XML">
	///		&lt;connectionStrings&gt;
	///			&lt;add name="Halide" connectionString="Data Source=server; Initial Catalog=database; User ID=username; Password=password; Connection Timeout=30;" providerName="System.Data.SqlClient" /&gt;
	///		&lt;/connectionStrings&gt;
    /// </code>
    /// </example>
    /// <example>
    /// Instantiate the class with an open Halide.SmartReader object.
    /// <code>
    /// Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM Data WHERE ID=5;");
    /// reader.Read()
    /// Halide.DataRow dataRow = new Halide.DataRow(reader);
    /// reader.Close();
    /// </code>
    /// Instantiate the class by passing it the value of the primary key
    /// of the record you wish to load, and the object name within the database.
    /// <code>
    /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
    /// Halide.DataRow dataRow = new Halide.DataRow("abcdefg", "Content");
    /// Halide.DataRow dataRow = new Halide.DataRow(dateValue, "Content");
    /// </code>
    /// Instantiate the class with a complete SQL statement.
    /// <code>
    /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Data WHERE ID=5;");
    /// </code>
    /// Instantiate the class with a Request object for reading in a submitted form.
	/// Note, it only reads in form fields whose names begin with "Halide_CMS_".
	/// The remainder of the name should be the name of the corresponding column in the database.
    /// <code>
    /// Halide.DataRow dataRow = new Halide.DataRow(Request);
    /// </code>
    /// Instantiate the class with no data structure.
    /// <code>
    /// Halide.DataRow dataRow = new Halide.DataRow();
    /// </code>
    /// </example>
	public class DataRow
	{
		#region VARIABLES AND INSTANTIATION METHODS

		private static string _version = "2008.07.31A";

		/// <summary>
		/// Return the version number of the class; read-only.
		/// </summary>
		/// <value>
		/// A string with the version number of the class.
		/// </value>
		public static string Version
		{
			get
			{
				return _version;
			}
		}

        /// <summary>
        /// Access an individual data column using this object (see the DatabaseItem object for methods and properties).
        /// The preferred way to access this object is using a DataRow object indexer.
        /// </summary>
        public DatabaseRow Column;

		/// <summary>
		/// How many fields have been loaded? Initialized after instantiation.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow.FieldCount.ToString());
        /// </code>
        /// </example>
        /// <returns>Number of fields loaded as an integer.</returns>
		public int FieldCount
		{
			get
			{
				return Column.FieldCount;
			}

            set
			{
                Column.FieldCount = value;
			}
		}

		/// <summary>
		/// Instantiate the class with no parameters.
		/// This option creates an empty data structure and is useful for creating new records.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.DataRow data = new Halide.DataRow();
		/// data.AddColumn("ParentID", "0");
		/// data.AddColumn("TopLevelParentID", "0");
		/// data.AddColumn("UserID", "2");
		/// data.AddColumn("Rewrite", Halide.Utility.MakeUniqueIdentifier());
		/// string retVal = data.Exec("sp_AddTempContent");
		/// Int64 RecordID;
		/// if (!String.IsNullOrEmpty(retVal))
		/// {
		///		RecordID = Convert.ToInt64(retVal);
		///	}
		/// </code>
		/// </example>
		public DataRow()
		{
			Column = new DatabaseRow();
		}

		/// <summary>
		/// Instantiate the class with a Halide.DataCon class variable
		/// to specify what should be loaded, and from where. You must
		/// specify either a SQL Command, or Table Name, Primary Key Name
		/// and Primary Key Value, at a minimum. SQL command overrides all
		/// other parameters.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.DataCon dataCon = new Halide.DataCon();
		/// dataCon.ConnectionName = "MyConnection";
		/// dataCon.TableName = "News";
		/// dataCon.PrimaryKeyName = "News_ID";
		/// dataCon.PrimaryKeyValue = "15";
		/// dataCon.FormTemplateName = "NewsAdmin.xml";
		/// Halide.DataRow data = new Halide.DataRow(dataCon);
		/// </code>
		/// </example>
		/// <param name="dataCon">Halide.DataCon object with database settings.</param>
		public DataRow(DataCon dataCon)
		{
			string SQL = "";

			// If DataCon has a SQL command passed...
			if (!String.IsNullOrEmpty(dataCon.SQLCommand))
			{
				SQL = dataCon.SQLCommand;
			}

			else
			{
				// If DataCon object has table name, primary key name and value passed...
				if (!String.IsNullOrEmpty(dataCon.PrimaryKeyName) && !String.IsNullOrEmpty(dataCon.PrimaryKeyValue) && !String.IsNullOrEmpty(dataCon.TableName))
				{
					SQL = "SELECT TOP 1 * FROM " + dataCon.TableName + " WHERE " + dataCon.PrimaryKeyName + "='" + dataCon.PrimaryKeyValue.Replace("'", "''") + "'";
				}

				else
				{
					// If DataCon object has table name and primary key value passed...
					if (!String.IsNullOrEmpty(dataCon.PrimaryKeyValue) && !String.IsNullOrEmpty(dataCon.TableName))
					{
						Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + dataCon.TableName, true);
						reader.Read();
						string PrimaryKey = reader.GetPrimaryKeyName();
						reader.Close();

						SQL = "SELECT TOP 1 * FROM " + dataCon.TableName + " WHERE " + PrimaryKey + "='" + dataCon.PrimaryKeyValue.Replace("'", "''") + "'";
					}

					// If all else fails, load an empty object
					else
					{
						Column = new DatabaseRow();
					}
				}
			}

			// Instantiate the data object if valid parameters have been passed
			if (!String.IsNullOrEmpty(SQL))
			{
				Halide.SmartReader reader = new Halide.SmartReader(SQL, true, dataCon.ConnectionName);
				reader.Read();
				Column = new DatabaseRow(reader);
				reader.Close();
			}	
		}

		/// <summary>
		/// Instantiate the class with an open SmartReader object.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM Data WHERE ID=5;");
        /// reader.Read()
        /// Halide.DataRow dataRow = new Halide.DataRow(reader);
        /// reader.Close();
        /// </code>
        /// </example>
        /// <param name="reader">Halide.SmartReader object, opened and read().</param>
		public DataRow(Halide.SmartReader reader)
		{
			Column = new DatabaseRow(reader);
        }

   		/// <summary>
		/// Instantiate the class with a Request object reference.
        /// This is used to read in a submitted form, identify the form fields' data types via the database object.
		/// Only reads in form fields whose names begin with "Halide_CMS_". The remainder of the name should be the
		/// name of the corresponding column in the database.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(Request, "Content");
        /// </code>
        /// </example>
        /// <param name="request">HttpRequest object with submitted form data.</param>
        /// <param name="DataObject">Name of database object to use for identifiying column information.</param>
        public DataRow(HttpRequest request, string DataObject)
		{
            // Load first row in for field definitions
            Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject, true);
            reader.Read();
            Column = new DatabaseRow(reader);
            reader.Close();

            // Clear out read column values and replace with submitted form field values
            LoadForm(request);
        }

		/// <summary>
		/// Instantiate the class with a Control object reference.
		/// This is used to read in a group of controls within the parent control,
		/// identify and load any form fields control types that have been submitted and their
		/// data types via the database object.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.DataRow dataRow = new Halide.DataRow(form1, "Content");
		/// </code>
		/// </example>
		/// <param name="page">Control object with submitted form data.</param>
		/// <param name="DataObject">Name of database object to use for identifiying column information.</param>
		public DataRow(Control p, string DataObject)
		{
			// Load first row in for field definitions
			Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject, true);
			reader.Read();
			Column = new DatabaseRow(reader);
			reader.Close();

			// Clear out read column values and replace with submitted form field values
			LoadForm(p);
		}

		/// <summary>
		/// Instantiate the class with the value of the primary key for the record to be read
		/// and the name of the database object from which to read.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// </code>
        /// </example>
		/// <param name="primaryKeyValue">value of the primary key for the record to be read.</param>
        /// <param name="DataObject">Name of database object from which to read.</param>
		public DataRow(Int64 primaryKeyValue, string DataObject)
		{
			// Identify primary key
            Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject, true);
			reader.Read();
			string PrimaryKey = reader.GetPrimaryKeyName();
			reader.Close();

            reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject + " WHERE " + PrimaryKey + "=" + primaryKeyValue.ToString(), true);
			reader.Read();
			Column = new DatabaseRow(reader);
			reader.Close();
        }

		/// <summary>
		/// Instantiate the class with the value of the primary key for the record to be read
		/// and the name of the database object from which to read.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// </code>
        /// </example>
		/// <param name="primaryKeyValue">value of the primary key for the record to be read.</param>
        /// <param name="DataObject">Name of database object from which to read.</param>
		public DataRow(int primaryKeyValue, string DataObject)
		{
			// Identify primary key
            Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject, true);
			reader.Read();
			string PrimaryKey = reader.GetPrimaryKeyName();
			reader.Close();

            reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject + " WHERE " + PrimaryKey + "=" + primaryKeyValue.ToString(), true);
			reader.Read();
			Column = new DatabaseRow(reader);
			reader.Close();
        }

		/// <summary>
		/// Instantiate the class with the value of the primary key for the record to be read
		/// and the name of the database object from which to read.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(DateTime.Now(), "Content");
        /// </code>
        /// </example>
		/// <param name="primaryKeyValue">value of the primary key for the record to be read.</param>
        /// <param name="DataObject">Name of database object from which to read.</param>
		public DataRow(DateTime primaryKeyValue, string DataObject)
		{
			// Identify primary key
            Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject, true);
			reader.Read();
			string PrimaryKey = reader.GetPrimaryKeyName();
			reader.Close();

            reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject + " WHERE " + PrimaryKey + "='" + primaryKeyValue.ToString() + "'", true);
			reader.Read();
			Column = new DatabaseRow(reader);
			reader.Close();
        }

		/// <summary>
		/// Instantiate the class with the value of the primary key for the record to be read
		/// and the name of the database object from which to read.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("abcdefg", "Content");
        /// </code>
        /// </example>
		/// <param name="primaryKeyValue">value of the primary key for the record to be read.</param>
        /// <param name="DataObject">Name of database object from which to read.</param>
		public DataRow(string primaryKeyValue, string DataObject)
		{
			// Identify primary key
            Halide.SmartReader reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject, true);
			reader.Read();
			string PrimaryKey = reader.GetPrimaryKeyName();
			reader.Close();

            reader = new Halide.SmartReader("SELECT TOP 1 * FROM " + DataObject + " WHERE " + PrimaryKey + "='" + primaryKeyValue + "'", true);
			reader.Read();
			Column = new DatabaseRow(reader);
			reader.Close();
        }

		/// <summary>
		/// Instantiate the class with a complete SQL statement.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Data WHERE ID=5;");
        /// </code>
        /// </example>
		/// <param name="SQLCommand">Complete SQL command string to use for reading the desired row.</param>
		public DataRow(string SQLCommand)
		{
			Halide.SmartReader reader = new Halide.SmartReader(SQLCommand, true);
			reader.Read();
			Column = new DatabaseRow(reader);
			reader.Close();
        }

		#endregion

        #region INDEXERS

        /// <summary>
        /// Used to quickly retrieve a single column from the database row that has been read.
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        ///
        /// // Display column 0's column name.
        /// Response.Write (dataRow[0].ColumnName);
        ///
        /// // Display column 0's value.
        /// Response.Write (dataRow[0].Value);
        ///
        /// // Display the maximum number of characters the column 0 can hold.
        /// Response.Write (dataRow[0].ColumnSize.ToString());
        /// </code>
        /// </example>
        /// <param name="x">Index number of the column to return.</param>
        /// <returns>A Halide.DatabaseItem object reference.</returns>
		public DatabaseItem this[int x]
		{
			get
			{
				return Column[x];
			}
		}

        /// <summary>
        /// Used to quickly retrieve a single column from the database row that has been read.
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        ///
        /// // Display the value of column "Headline".
        /// Response.Write (dataRow["Headline"].Value);
        ///
        /// // Display the maximum number of characters the column "Headline" can hold.
        /// Response.Write (dataRow["Headline"].ColumnSize.ToString());
        /// </code>
        /// </example>
        /// <param name="ColumnName">Name of the column to return.</param>
        /// <returns>A Halide.DatabaseItem object reference.</returns>
        public DatabaseItem this[string ColumnName]
		{
			get
			{
				return Column[ColumnName.ToLower()];
			}
		}

		#endregion

        #region METHODS

        /// <summary>
        /// Load content from a posted form. This is done automatically when instantiating the class
        /// with a request object, so use this method with care.
        /// </summary>
        /// <param name="request">HttpRequest object.</param>
        /// <returns>true on success; false on failure.</returns>
        public bool LoadForm(HttpRequest request)
        {
            bool retVal = true;

            for (int x = 0; x < this.FieldCount; x++)
            {
                this[x].Value = "";
            }

            foreach (string fieldName in request.Form)
            {
				if (fieldName.Contains("Halide_CMS_") || fieldName.Contains("Carbide_CMS_"))
				{
					string fn = fieldName.Replace("Halide_CMS_", "");
					fn = fn.Replace("Carbide_CMS_", "").ToLower();

					if (fn.Contains("$"))
					{
						string[] fna = fn.Split('$');
						fn = fna[1];
					}

					if (!this.ColumnExists(fn))
					{
						this.AddColumn(fn, request.Form[fieldName]);
					}

					else
					{
						this[fn].Value = request.Form[fieldName];
					}
				}
            }

            return retVal;
        }

		/// <summary>
		/// Load content from controls. This is done automatically when instantiating the class
		/// with a Control object, so use this method with care.
		/// </summary>
		/// <param name="p">Page object.</param>
		/// <returns>true on success; false on failure.</returns>
		public bool LoadForm(Control p)
		{
			bool retVal = false;

			for (int x = 0; x < this.FieldCount; x++)
			{
				this[x].Value = "";
			}

			foreach (Control c in p.Controls)
			{
				if (!String.IsNullOrEmpty(c.ID))
				{
					retVal = true;
					string val = "";

					switch (c.GetType().ToString())
					{
						case "System.Web.UI.HtmlControls.HtmlInputHidden":

							HtmlInputHidden hhid = (HtmlInputHidden)c;
							val = hhid.Value;
							break;

						case "System.Web.UI.HtmlControls.HtmlInputPassword":

							HtmlInputPassword hpas = (HtmlInputPassword)c;
							val = hpas.Value;
							break;

						case "System.Web.UI.HtmlControls.HtmlInputText":

							HtmlInputText htb = (HtmlInputText)c;
							val = htb.Value;
							break;
	
						case "System.Web.UI.WebControls.TextBox":
							
							TextBox tb = (TextBox)c;
							val = tb.Text;
							break;

						case "System.Web.UI.WebControls.HiddenField":

							HiddenField hidtb = (HiddenField)c;
							val = hidtb.Value;
							break;

						case "System.Web.UI.WebControls.CheckBoxList":

							CheckBoxList cb = (CheckBoxList)c;
							val = cb.SelectedValue;
							break;

						case "System.Web.UI.WebControls.RadioButtonList":

							RadioButtonList rb = (RadioButtonList)c;
							val = rb.SelectedValue;
							break;

						case "System.Web.UI.WebControls.ListBox":

							ListBox lb = (ListBox)c;
							val = lb.SelectedValue;
							break;

						case "System.Web.UI.WebControls.DropDownList":

							DropDownList db = (DropDownList)c;
							val = db.SelectedValue;
							break;

						case "System.Web.UI.HtmlControls.HtmlSelect":

							HtmlSelect hdb = (HtmlSelect)c;
							val = hdb.Value;
							break;

						case "System.Web.UI.WebControls.CheckBox":

							CheckBox chb = (CheckBox)c;
							val = chb.Checked.ToString();
							break;

						case "System.Web.UI.HtmlControls.HtmlInputCheckBox":

							HtmlInputCheckBox hchb = (HtmlInputCheckBox)c;
							val = hchb.Checked.ToString();
							break;

						case "System.Web.UI.WebControls.RadioButton":

							RadioButton rbb = (RadioButton)c;
							val = rbb.Checked.ToString();
							break;

						case "System.Web.UI.HtmlControls.HtmlInputRadioButton":

							HtmlInputRadioButton hrbb = (HtmlInputRadioButton)c;
							val = hrbb.Checked.ToString();
							break;
					}

					if (!this.ColumnExists(c.ID))
					{
						this.AddColumn(c.ID, val);
					}

					else
					{
						this[c.ID].Value = val;
					}
				}
			}

			return retVal;
		}

		/// <summary>
		/// Determine if a column of a specific name exists in the data set.
		/// </summary>
		/// <param name="ColumnName">Name of the database column.</param>
		/// <returns>true or false.</returns>
		public bool ColumnExists(string ColumnName)
		{
			bool returnVal = false;

			for (int x = 0; x < this.FieldCount; x++)
			{
				if (this[x].ColumnName == ColumnName.ToLower())
					returnVal = true;
			}

			return returnVal;
		}

        /// <summary>
        /// Add a column to the data set in memory. This is useful when stored procedures used for
        /// saving data require optional parameters.
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Data WHERE ID=5;");
        /// dataRow.AddColumn("YourAge", "30");
        /// </code>
        /// </example>
        /// <param name="ColumnName">Name of the new column.</param>
        /// <param name="Value">Value of the new column.</param>
        /// <returns>true if successful, false if not.</returns>
        public bool AddColumn(string ColumnName, string Value)
        {
            return this.Column.AddColumn(ColumnName.ToLower(), Value);
        }

        /// <summary>
        /// Delete a column from the data set in memory.
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Data WHERE ID=5;");
        /// dataRow.DeleteColumn("YourAge");
        /// </code>
        /// </example>
        /// <param name="ColumnName">Name of the column to delete.</param>
        /// <returns>true if successful, false if not.</returns>
        public bool DeleteColumn(string ColumnName)
        {
			return this.Column.DeleteColumn(ColumnName.ToLower());
        }

        /// <summary>
        /// Delete a column from the data set in memory.
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Data WHERE ID=5;");
        /// dataRow.DeleteColumn(5);
        /// </code>
        /// </example>
        /// <param name="Index">Index number of the column to delete.</param>
        /// <returns>true if successful, false if not.</returns>
        public bool DeleteColumn(int Index)
        {
            return this.Column.DeleteColumn(Index);
        }

        /// <summary>
        /// Execute the specified stored procedure. Parameters used in the stored procedure
        /// must match column names in the currently loaded data row. If so, this method will
        /// automatically send the appropriate data to the procedure.
        /// <para>This method is useful for saving the record back to the database, marking a
		/// record for deletion, and more.</para>
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Content WHERE ID=5;");
        /// dataRow["Title"].Value = "NEW TITLE";
        /// dataRow.Exec("spu_SaveRecord");
        /// </code>
        /// </example>
        /// <param name="spName">Name of the stored procedure.</param>
		/// <param name="SendAll">Send all data fields without auto-detecting SP parameters.</param>
        /// <returns>Return value of the stored procedure as a string (first column of a rowset),
		/// or any error message.</returns>
		public string Exec(string spName, bool SendAll)
		{
			string returnVal = "";

			SqlConnection con = new SqlConnection(Halide.Database.SqlConnectionString);
			SqlCommand cmd = new SqlCommand(spName, con);
			cmd.CommandType = CommandType.StoredProcedure;

			string[,] _params = Halide.Database.SqlGetSpParams(spName);

			try
			{
				if (SendAll)
				{
					for (int x = 0; x < this.FieldCount; x++)
					{
						string ParamVal = "";

						ParamVal = Halide.StringJuggler.HtmlEncodeSymbols(this[x].Value.ToString());

						cmd.Parameters.AddWithValue("@" + this[x].ColumnName, ParamVal);
					}
				}

				else
				{
					for (int x = 0; x < _params.Length / 2; x++)
					{
						string ParamVal = "";

						if (ColumnExists(_params[x, 0].Replace("@", "")))
							ParamVal = Halide.StringJuggler.HtmlEncodeSymbols(this[_params[x, 0].Replace("@", "").ToLower()].Value.ToString());

						cmd.Parameters.AddWithValue(_params[x, 0], ParamVal);
					}
				}

				con.Open();
				returnVal = cmd.ExecuteScalar().ToString();
				con.Close();
			}
			catch (Exception e)
			{
				returnVal += e.ToString();
			}

			return returnVal;
		}

		/// <summary>
		/// Execute the specified stored procedure. Parameters used in the stored procedure
		/// must match column names in the currently loaded data row. If so, this method will
		/// automatically send the appropriate data to the procedure.
		/// <para>This method is useful for saving the record back to the database, marking a
		/// record for deletion, and more.</para>
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Content WHERE ID=5;");
		/// dataRow["Title"].Value = "NEW TITLE";
		/// dataRow.Exec("spu_SaveRecord");
		/// </code>
		/// </example>
		/// <param name="spName">Name of the stored procedure.</param>
		/// <returns>Return value of the stored procedure as a string, or an empty string if none.</returns>
		public string Exec(string spName)
		{
			return Exec(spName, false);
		}

        /// <summary>
        /// Strips specific field data of dangerous script markup.
        /// </summary>
        /// <param name="ColumnName">Name of the data column to process.</param>
        public void ScriptSanitize(string ColumnName)
        {
			this[ColumnName.ToLower()].Value = Halide.StringJuggler.StripScripts(this[ColumnName.ToLower()].Value);
        }

        /// <summary>
        /// Strips specific field data of dangerous script markup.
        /// </summary>
        /// <param name="ColumnIndex">Index of the data column to process.</param>
        public void ScriptSanitize(int ColumnIndex)
        {
            this[ColumnIndex].Value = Halide.StringJuggler.StripScripts(this[ColumnIndex].Value);
        }

        /// <summary>
        /// Strips specific field data of markup.
        /// </summary>
        /// <param name="ColumnName">Name of the data column to process.</param>
        public void MarkupSanitize(string ColumnName)
        {
			this[ColumnName.ToLower()].Value = Halide.StringJuggler.StripHtml(this[ColumnName.ToLower()].Value, false, false);
        }

        /// <summary>
        /// Strips specific field data of markup.
        /// </summary>
        /// <param name="ColumnIndex">Index of the data column to process.</param>
        public void MarkupSanitize(int ColumnIndex)
        {
            this[ColumnIndex].Value = Halide.StringJuggler.StripHtml(this[ColumnIndex].Value, false, false);
        }

        /// <summary>
        /// Strips specific field data of dangerous email header text.
        /// </summary>
        /// <param name="ColumnName">Name of the data column to process.</param>
        public void EmailSanitize(string ColumnName)
        {
			this[ColumnName.ToLower()].Value = Halide.StringJuggler.StripEmailHeaderProperties(this[ColumnName.ToLower()].Value);
        }

        /// <summary>
        /// Strips specific field data of dangerous email header text.
        /// </summary>
        /// <param name="ColumnIndex">Index of the data column to process.</param>
        public void EmailSanitize(int ColumnIndex)
        {
            this[ColumnIndex].Value = Halide.StringJuggler.StripEmailHeaderProperties(this[ColumnIndex].Value);
        }

        /// <summary>
        /// Sanitizes specific field data for SQL use by doubling apostrophes.
        /// </summary>
        /// <param name="ColumnName">Name of the data column to process.</param>
        public void SQLSanitize(string ColumnName)
        {
			this[ColumnName.ToLower()].Value = Halide.Database.SqlClean(this[ColumnName.ToLower()].Value);
        }

        /// <summary>
        /// Sanitizes specific field data for SQL use by doubling apostrophes.
        /// </summary>
        /// <param name="ColumnIndex">Index of the data column to process.</param>
        public void SQLSanitize(int ColumnIndex)
        {
            this[ColumnIndex].Value = Halide.Database.SqlClean(this[ColumnIndex].Value);
        }

        /// <summary>
        /// Sanitization options enumeration used with the "Sanitize" method.
        /// </summary>
        [Flags]
        public enum SanitizeOptions
        {
            /// <summary>
            /// Strip any dangerous script content embedded within other tags.
            /// </summary>
            Scripts,

            /// <summary>
            /// Strip any potentially dangerous e-mail header text used to override
            /// e-mail submissions to send SPAM.
            /// </summary>
            Email,

            /// <summary>
            /// Convert single apostrophes to double, to prevent injection attacks.
            /// </summary>
            SQL,

            /// <summary>
            /// Strip any dangerous markup, like &lt;iframe&gt;, &lt;script&gt;, et al.
            /// </summary>
            Markup
        }

        /// <summary>
        /// Sanitize all fields for a specified type of data.
        /// </summary>
        /// <param name="options">SanitizeOptions enum value specifying which type of filtering to run.</param>
        public void Sanitize(SanitizeOptions options)
        {
            for (int x = 0; x < this.FieldCount; x++)
            {
                if (options == SanitizeOptions.Scripts) ScriptSanitize(x);
                if (options == SanitizeOptions.Email) EmailSanitize(x);
                if (options == SanitizeOptions.SQL) SQLSanitize(x);
                if (options == SanitizeOptions.Markup) MarkupSanitize(x);
            }
        }

        /// <summary>
        /// Get the primary key column name.
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Content WHERE ID=5;");
        /// string result = dataRow.GetPrimaryKeyName();
        /// </code>
        /// </example>
        /// <returns>A string with the column name of the primary key.</returns>
        public string GetPrimaryKeyName()
        {
            string retVal = "";

            for (int x = 0; x < this.FieldCount; x++)
                if (this[x].IsPrimaryKey)
                    retVal = this[x].ColumnName;

            return retVal;
        }

		#endregion
	}

	/// <summary>
	/// Subclass which contains all of an individual column's data and specs
	/// This is the lowest rung on the class ladder. It represents a single column
	/// in a database row, containing the column name, value, and properties.
    /// <para>This class it never used directly, but as a subclass of an instantiated
    /// Halide.DataRow class object.</para>
	/// </summary>
	public class DatabaseItem
	{
		#region VARIABLES AND INSTANTIATION METHODS

		private string _ColumnName;
		private string _ColumnType;
		private int _ColumnSize;
		private string _SystemDataType;
		private string _Value;
		private bool _IsPrimaryKey = false;
		private bool _AllowsNulls = false;
		private bool _IsAutoIncrement = false;
		private bool _IsIdentity = false;

        /// <summary>
        /// Instantiation method not needed.
        /// </summary>
		public DatabaseItem()
		{
		}

		#endregion

		#region INDEXER

		/// <summary>
		/// Set or retrieve the value of the column.
		/// </summary>
        /// <value>
        /// A string value.
        /// </value>
        public string Value
		{
			get
			{
				return _Value;
			}

			set
			{
				_Value = value.ToString();
			}
		}

		#endregion

		/// <summary>
		/// Tests the column value for null or empty string.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].IsNullOrEmpty.ToString());
        /// </code>
        /// </example>
        /// <value>
        /// A boolean value.
        /// </value>
		public bool IsNullOrEmpty
		{
			get
			{
				return (string.IsNullOrEmpty(_Value) ? true : false);
			}
		}

		#region COLUMN PROPERTIES

		/// <summary>
		/// Get the name of the column.
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].ColumnName);
        /// </code>
        /// </example>
        /// <value>
        /// A string value.
        /// </value>
		public string ColumnName
		{
			get
			{
				return _ColumnName;
			}

			set
			{
				_ColumnName = value.ToLower();
			}
		}

		/// <summary>
		/// Can the column accept null values?
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].IsNullable.ToString());
        /// </code>
        /// </example>
        /// <value>
        /// A boolean value.
        /// </value>
        public bool IsNullable
		{
			get
			{
				return _AllowsNulls;
			}

			set
			{
				_AllowsNulls = value;
			}
		}

		/// <summary>
		/// Return the dimension (byte size) of the column (eg. varchar limit)
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].ColumnSize.ToString());
        /// </code>
        /// </example>
        /// <value>
        /// A 32-bit integer value.
        /// </value>
        public int ColumnSize
		{
			get
			{
				return _ColumnSize;
			}

			set
			{
				_ColumnSize = value;
			}
		}

		/// <summary>
		/// Return the SQL data type of the column (eg. varchar, bigint, etc.).
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].ColumnSQLType);
        /// </code>
        /// </example>
        /// <value>
        /// A string value.
        /// </value>
        public string ColumnSQLType
		{
			get
			{
				return _ColumnType;
			}

			set
			{
				_ColumnType = value;
			}
		}

		/// <summary>
		/// Return the .NET System data type of the column (eg. string, Int64, etc.).
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].SystemDataType);
        /// </code>
        /// </example>
        /// <value>
        /// A string value.
        /// </value>
        public string SystemDataType
		{
			get
			{
				return _SystemDataType;
			}

			set
			{
				_SystemDataType = value;
			}
		}

		/// <summary>
		/// Does the column auto increment?
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].IsAutoIncrementing.ToString());
        /// </code>
        /// </example>
        /// <value>
        /// A boolean value.
        /// </value>
        public bool IsAutoIncrementing
		{
			get
			{
				return _IsAutoIncrement;
			}

			set
			{
				_IsAutoIncrement = value;
			}
		}

		/// <summary>
		/// Is the column an identity?
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].IsIdentity.ToString());
        /// </code>
        /// </example>
        /// <value>
        /// A boolean value.
        /// </value>
        public bool IsIdentity
		{
			get
			{
				return _IsIdentity;
			}

			set
			{
				_IsIdentity = value;
			}
		}

		/// <summary>
		/// Is the column a primary key?
		/// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
        /// Response.Write (dataRow[5].IsPrimaryKey.ToString());
        /// </code>
        /// </example>
        /// <value>
        /// A boolean value.
        /// </value>
        public bool IsPrimaryKey
		{
			get
			{
				return _IsPrimaryKey;
			}

			set
			{
				_IsPrimaryKey = value;
			}
		}

		/// <summary>
		/// Determine if this a numeric SQL column type.
		/// </summary>
		/// <example>
		/// <code>
		/// Halide.DataRow dataRow = new Halide.DataRow(115, "Content");
		/// Response.Write (dataRow[5].IsNumeric.ToString());
		/// </code>
		/// </example>
		/// <value>
		/// A boolean value.
		/// </value>
		public bool IsNumeric
		{
			get
			{
				if (ColumnSQLType.ToLowerInvariant().EndsWith("int") || ColumnSQLType.ToLowerInvariant().Contains("float") || ColumnSQLType.ToLowerInvariant().Contains("money") || ColumnSQLType.ToLowerInvariant().Contains("numeric") || ColumnSQLType.ToLowerInvariant().Contains("real") || ColumnSQLType.ToLowerInvariant().Contains("decimal"))
				{
					return true;
				}
				else
				{
					return false;
				}
			}
		}

		#endregion
	}

	/// <summary>
	/// Subclass which contains all of an individual row's columns as individual DatabaseItem objects
	/// </summary>
	public class DatabaseRow
	{
		#region VARIABLES AND INSTANTIATION METHODS

		/// <summary>
		/// The data item collection represents an array of all the columns in the row
        /// read from the database. This variable is generally used by the class, and should
        /// not be used by the programmer.
		/// </summary>
		[field: CLSCompliant(false)]
		public DatabaseItem[] _dataItems;
		
        /// <summary>
        /// A normal string array does not have the ability to be referenced by value.
        /// The sorted list stores the names and string array index numbers, acting as
        /// an index into the string array. This variable is generally used by the class,
        /// and should not be used by the programmer.
        /// </summary>
		[field: CLSCompliant(false)]
		public SortedList _dataIndex = new SortedList();
		
        private int _fieldCount = 0;

        /// <summary>
        /// Initialize the DatabaseRow with an empty row.
        /// </summary>
		public DatabaseRow()
		{
		}

        /// <summary>
        /// Initialize the DatabaseRow with a single row form a Halide.SmartReader object.
        /// </summary>
        /// <param name="reader">An open Halide.SmartReader object that can be used to read a row from a database.</param>
        public DatabaseRow(Halide.SmartReader reader)
		{
			_fieldCount = reader.FieldCount;

			try
			{
				_dataItems = new DatabaseItem[_fieldCount];

				if (_dataIndex != null) _dataIndex.Clear();

				for (int x = 0; x < _fieldCount; x++)
				{
					_dataItems[x] = new DatabaseItem();

					string columnName = reader.ColumnName(x).ToLower();

					_dataIndex.Add(columnName, x);

					_dataItems[x].IsNullable = reader.GetColumnAllowNulls(columnName);
					_dataItems[x].ColumnName = columnName;
					_dataItems[x].ColumnSize = reader.GetColumnSize(columnName);
					_dataItems[x].ColumnSQLType = reader.GetDataTypeName(columnName).ToLower();
					_dataItems[x].IsAutoIncrementing = reader.GetColumnIsAutoIncrement(columnName);
					_dataItems[x].IsIdentity = reader.GetColumnIsIdentity(columnName);
					_dataItems[x].IsPrimaryKey = reader.GetColumnIsKey(columnName);
					_dataItems[x].SystemDataType = reader.GetColumnSystemDataType(columnName).ToLower();
					_dataItems[x].Value = reader.GetString(columnName);
				}
			}
			catch
			{
			}
		}

		#endregion

		/// <summary>
		/// How many fields have been loaded? Initialized after instantiation.
		/// </summary>
        /// <value>
        /// A 32-bit integer value.
        /// </value>
		public int FieldCount
		{
			get
			{
				return _fieldCount;
			}

   			set
			{
				_fieldCount = value;
			}
        }

		#region INDEXERS

		/// <summary>
		/// Return the DatabaseItem of the specified name. If you try to retrieve an item that
        /// does not exist, one is created automatically.
		/// </summary>
		/// <param name="ColumnName">The column name of the DatabaseItem.</param>
		public DatabaseItem this[string ColumnName]
		{
			get
			{
				if (_dataIndex.Contains(ColumnName.ToLower()))
				{
					return _dataItems[(int)_dataIndex[ColumnName.ToLower()]];
				}

				else
				{
					AddColumn(ColumnName.ToLower(), "");
					return _dataItems[(int)_dataIndex[ColumnName.ToLower()]];
				}
			}
		}

		/// <summary>
		/// Return the DatabaseItem at the specified index, or set the value.
		/// </summary>
		/// <param name="ColumnIndex">The index number of the column from which to retrieve the DatabaseItem.</param>
		public DatabaseItem this[int ColumnIndex]
		{
			get
			{
				return _dataItems[ColumnIndex];
			}
		}

		#endregion

        #region METHODS

        /// <summary>
        /// Add a column to the data set in memory. This is useful when stored procedures used for
        /// saving data require optional parameters.
        /// <para>The new column will act just like a real SQL data column, and you may
        /// want to add a faux SQL data type to its properties, as well as any other
        /// properties, so that you can manipulate it properly.</para>
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Content WHERE ID=5;");
        /// dataRow.Column.AddColumn("YourAge", "30");
        /// dataRow["YourAge"].ColumnSQLType = "bigint";
        /// </code>
        /// </example>
        /// <param name="ColName">Name of the new column.</param>
        /// <param name="Value">Value of the new column.</param>
        /// <returns>true if successful, false if not.</returns>
        public bool AddColumn(string ColName, string Value)
        {
            bool retVal = false;
            int oldFieldCount = _fieldCount;
            string newVal = Value;
			string ColumnName = ColName.ToLower();

            if (String.IsNullOrEmpty(newVal))
                newVal = "";

            if (!String.IsNullOrEmpty(ColumnName))
            {
                try
                {
                    #region COPY DATA INTO NEW ARRAY, ONE LARGER THAN CURRENT

                    DatabaseItem[] _dataItemsX;
                    _dataItemsX = new DatabaseItem[_fieldCount + 1];

                    for (int x = 0; x < _fieldCount; x++)
                    {
                        _dataItemsX[x] = new DatabaseItem();
                        _dataItemsX[x] = _dataItems[x];
                    }

                    #endregion

                    #region ADD NEW ENTRY TO END OF NEW ARRAY

                    _dataItemsX[_fieldCount] = new DatabaseItem();
                    _dataItemsX[_fieldCount].ColumnName = ColumnName;
                    _dataItemsX[_fieldCount].Value = newVal;
					_dataItemsX[_fieldCount].ColumnSize = 0;
					_dataItemsX[_fieldCount].ColumnSQLType = "";
					_dataItemsX[_fieldCount].IsAutoIncrementing = false;
					_dataItemsX[_fieldCount].IsIdentity = false;
					_dataItemsX[_fieldCount].IsNullable = false;
					_dataItemsX[_fieldCount].IsPrimaryKey = false;
					_dataItemsX[_fieldCount].SystemDataType = "";

                    _dataIndex.Add(ColumnName, _fieldCount);
                    _fieldCount++;

                    #endregion

                    #region ENLARGE OLD ARRAY; COPY NEW ARRAY BACK INTO OLD ONE

                    _dataItems = new DatabaseItem[_fieldCount];

                    for (int x = 0; x < _fieldCount; x++)
                    {
                        _dataItems[x] = new DatabaseItem();
                        _dataItems[x] = _dataItemsX[x];
                    }

                    #endregion

                    retVal = true;
                }

                catch
                {
                    _fieldCount = oldFieldCount;
                }
            }

            return retVal;
        }

        /// <summary>
        /// Delete a column from the data set in memory.
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Content WHERE ID=5;");
        /// dataRow.Column.DeleteColumn("MyAge");
        /// </code>
        /// </example>
        /// <param name="ColName">Name of the column to delete.</param>
        /// <returns>true if successful, false if not.</returns>
        public bool DeleteColumn(string ColName)
        {
            bool retVal = false;
            int oldFieldCount = _fieldCount;
			string ColumnName = ColName.ToLower();

            if (!String.IsNullOrEmpty(ColumnName))
            {
                if (_dataIndex[ColumnName] != null)
                {
                    try
                    {
                        #region COPY DATA INTO NEW ARRAY, EXCEPT FOR DELETED COLUMN

                        DatabaseItem[] _dataItemsX;
                        _dataItemsX = new DatabaseItem[_fieldCount - 1];
                        _dataIndex.Clear();

                        int counter = 0;

                        for (int x = 0; x < _fieldCount; x++)
                        {
                            if (_dataItems[x].ColumnName != ColumnName)
                            {
                                _dataItemsX[counter] = new DatabaseItem();
                                _dataItemsX[counter] = _dataItems[x];
                                _dataIndex.Add(_dataItemsX[counter].ColumnName, counter);
                                counter++;
                            }
                        }

                        #endregion

                        #region RESIZE OLD ARRAY; COPY NEW ARRAY BACK INTO OLD ONE

                        _fieldCount--;

                        _dataItems = new DatabaseItem[_fieldCount];

                        for (int x = 0; x < _fieldCount; x++)
                        {
                            _dataItems[x] = new DatabaseItem();
                            _dataItems[x] = _dataItemsX[x];
                        }

                        #endregion

                        retVal = true;
                    }

                    catch
                    {
                        _fieldCount = oldFieldCount;
                    }
                }
            }

            return retVal;
        }

        /// <summary>
        /// Delete a column from the data set in memory.
        /// </summary>
        /// <example>
        /// <code>
        /// Halide.DataRow dataRow = new Halide.DataRow("SELECT TOP 1 * FROM Content WHERE ID=5;");
        /// dataRow.Column.DeleteColumn(5);
        /// </code>
        /// </example>
        /// <param name="Index">Index number of the column to delete.</param>
        /// <returns>true if successful, false if not.</returns>
        public bool DeleteColumn(int Index)
        {
            bool retVal = false;
            string ColumnName = _dataIndex.GetKey(Index).ToString();

            if (!String.IsNullOrEmpty(ColumnName))
                retVal = DeleteColumn(ColumnName);

            return retVal;
        }

        #endregion
    }

// Outer namespace
}